# _*_ coding:utf-8 _*_
# @File  : daily_quotes.py
# @Time  : 2021-06-28  08:54
# @Author: zizle

# 日行情数据
import datetime
import os

import pandas as pd
from fastapi import APIRouter, Query, Depends, Body, UploadFile, Form
from db import FAConnection
from interfaces.depends import admin_logged_require, verify_date
from status import r_status
from spiders import futures_daily_quotes
from configs import APP_DIR

daily_quotes_api = APIRouter()


# --------------------- 上传行情数据文件 --------------------

@daily_quotes_api.post('/quotes-file/', summary='上传行情文件')
async def save_daily_quotes_file(person: dict = Depends(admin_logged_require), ex: str = Query(...),
                                 date: datetime.datetime = Depends(verify_date), quotes_file: UploadFile = Form(...)):
    if ex not in ['cffex', 'czce', 'dce', 'shfe']:
        return {'code': r_status.NOT_CONTENT, 'message': '上传失败,param `ex` error!'}
    suffix = os.path.splitext(quotes_file.filename)[1]
    temp_filepath = os.path.join(APP_DIR, f'temp/{ex}/daily/{date.strftime("%Y%m%d")}{suffix}')
    content = await quotes_file.read()  # 将文件保存到目标位置
    with open(temp_filepath, "wb") as fp:
        fp.write(content)
    await quotes_file.close()
    return {'code': r_status.CREATED_SUCCESS, 'message': '上传成功!'}

# --------------------- 查询/处理/保存 行情数据 --------------------


@daily_quotes_api.get('/daily-quotes/', summary='获取日行情数据')
async def get_daily_quotes(date: str = Query(...), ex: str = Query(...)):
    try:
        quotes_date = datetime.datetime.strptime(date, '%Y-%m-%d')
    except ValueError:
        return {'code': r_status.VALIDATE_ERROR, 'message': 'Query param date can not format `%Y-%m-%d`!', 'data': []}
    ex = ex.upper()
    # 获取数据
    if ex == 'CZCE':
        spider = futures_daily_quotes.CZCEDailyQuotes(parse_date=quotes_date)
    elif ex == 'DCE':
        spider = futures_daily_quotes.DCEDailyQuotes(parse_date=quotes_date)
    elif ex == 'SHFE':
        spider = futures_daily_quotes.SHFEDailyQuotes(parse_date=quotes_date)
    elif ex == 'CFFEX':
        spider = futures_daily_quotes.CFFEXDailyQuotes(parse_date=quotes_date)
    else:
        return {'code': r_status.VALIDATE_ERROR, 'message': 'ex validate error!', 'data': []}
    rep_data, msg = spider.get_daily_quotes()
    # 处理数据
    df = pd.DataFrame(rep_data)
    data_str = quotes_date.strftime('%Y-%m-%d')
    df['quotes_date'] = [data_str for _ in range(df.shape[0])]
    df.sort_values(by=['variety_en'], inplace=True)
    rep_data = df.to_dict(orient='records')
    return {'code': r_status.SUCCESS, 'message': f'获取{date}-{ex}的日行情数据成功!', 'data': rep_data}


@daily_quotes_api.post('/daily-quotes/', summary='保存日行情数据')
def save_daily_quotes(quotes_data: list = Body(...)):
    if len(quotes_data) < 1:
        return {'code': r_status.CREATED_SUCCESS, 'message': '没有发现可以保存的行情数据。', 'count': 0}
    # 处理数据
    df = pd.DataFrame(quotes_data)
    df['quotes_ts'] = df['quotes_date'].apply(lambda x: int(datetime.datetime.strptime(x, '%Y-%m-%d').timestamp()))
    df['creator'] = 1

    del df['quotes_date']
    save_data = df.to_dict(orient='records')
    first_data = save_data[0]
    db = FAConnection(conn_name='Save Futures Daily Quotes')
    # 查询一下这个交易所，这个数据是否存在，存在证明已经保存过，不做保存（另外，数据库重复自己也有校验）
    exist_sql = "SELECT id FROM dat_futures_daily_quotes WHERE quotes_ts=%s AND variety_en=%s LIMIT 1;"
    exist = db.query(exist_sql, param=[first_data['date'], first_data['variety_en']], fetchone=True, keep_conn=True)[0]
    if exist:
        db.close()
        return {'code': r_status.CREATED_SUCCESS, 'message': '该交易所数据已存在，不要重复保存。', 'count': 0}
    # 保存数据
    save_sql = 'INSERT IGNORE INTO dat_futures_daily_quotes (creator,quotes_ts,variety_en,contract,pre_settlement,' \
               'open_price,highest,lowest,close_price,settlement,trade_volume,trade_price,position_volume,' \
               'increase_volume) VALUES (%(creator)s,%(quotes_ts)s,%(variety_en)s,%(contract)s,%(pre_settlement)s,' \
               '%(open_price)s,%(highest)s,%(lowest)s,%(close_price)s,%(settlement)s,%(trade_volume)s,%(trade_price)s,' \
               '%(position_volume)s,%(increase_volume)s);'

    count, _ = db.insert(save_sql, param=save_data, many=True)
    return {'code': r_status.CREATED_SUCCESS, 'message': f'保存入库数量{count}！', 'count': count}


# --------------------- 查询/维护 行情数据 --------------------

@daily_quotes_api.get('/', summary='查询行情数据')
async def get_futures_quotes(date: str = Query(...)):
    try:
        quotes_date = datetime.datetime.strptime(date, '%Y-%m-%d')
    except ValueError:
        return {'code': r_status.VALIDATE_ERROR, 'message': 'Query param date can not format `%Y-%m-%d`!', 'data': []}
    query_sql = 'SELECT id,quotes_ts,variety_en,contract,pre_settlement,open_price,highest,lowest,close_price,' \
                'settlement,trade_volume,trade_price,position_volume,increase_volume ' \
                'FROM dat_futures_daily_quotes ' \
                'WHERE quotes_ts=%s;'
    db = FAConnection(conn_name='Query Quotes')
    records = db.query(query_sql, [int(quotes_date.timestamp())])
    df = pd.DataFrame(records)
    if df.empty:
        return {'code': r_status.SUCCESS, 'message': '查询行情数据为空!', 'data': []}
    df['quotes_date'] = df['quotes_ts'].apply(lambda x: datetime.datetime.fromtimestamp(x).strftime('%Y-%m-%d'))
    rep_data = df.to_dict(orient='records')
    return {'code': r_status.SUCCESS, 'message': '查询行情数据成功!', 'data': rep_data}
